//
// Created by gogoqiu on 2022/3/23.
//

#include "SqliteQueryEngine.h"
#include "utils.h"

#define SQLITE_PATH "preference.db"

int SqliteQueryEngine::QueryString(char *key, char *result) {
    //char buf[VALUE_MAX_LENGTH];

    return 0;
}

int SqliteQueryEngine::QueryBoolean(char *key, bool *result) {
    //char buf[VALUE_MAX_LENGTH];

    return 0;
}

//private, core
int SqliteQueryEngine::QueryValue(char *key, char *result) {

    return 0;
}

SqliteQueryEngine::SqliteQueryEngine(const wxString& AppId) {
    this->appId = AppId.Clone();
    int ret = sqlite3_open( SQLITE_PATH, &m_pDb );
    wxString curPath = GetWorkDirectory( );
    wxString temp;
    wxString db_path( SQLITE_PATH );
    temp.sprintf( L"Preference路径: %s%s", curPath, db_path );
    printf( "%s\n", temp.c_str().AsChar() );
    if( ret != SQLITE_OK ){
        //or call the signal to break all execute
        wxMessageBox( wxT("打开Preferences数据库失败!，退出"), wxT("错误"), wxICON_ERROR );
        throw "打开Preferences数据库失败!";
        //exit(-1);
    }
}

SqliteQueryEngine::~SqliteQueryEngine() {
    if( m_pDb ) {
        sqlite3_close(m_pDb);
    }
}

/*
 *
 */
int SqliteQueryEngine::InitTable(){
    char *errmsg;
    /* 创建表 */
    const char *sql = "CREATE TABLE preferences("
                      "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                      "appId TINYTEXT,"
                      "key TINYTEXT,"
                      "view_type TINYINT,"
                      "defaultValue TINYTEXT,"
                      "value TINYTEXT ,"
                      "prompt TINYTEXT,"
                      "description TINYTEXT );" ;

    int ret = sqlite3_exec( m_pDb,sql,nullptr, nullptr, &errmsg );
    if( ret!=SQLITE_OK ) {
#ifdef _DEBUG_
        printf("%s\n", errmsg);
        sqlite3_free(errmsg);
        return -1;
#endif
    }
    return 0;
}

//如何关联日志
int SqliteQueryEngine::RunSql( const wxString& sql ){
    char *errmsg;
    const char *_sql;
    _sql = sql.mb_str(wxConvUTF8);
    int ret = sqlite3_exec(m_pDb, _sql,nullptr, nullptr, &errmsg);
    if( ret!=SQLITE_OK ) {
#ifdef _DEBUG_
        printf("%s\n%s\n", _sql, errmsg);
#endif
        sqlite3_free(errmsg);
        return -1;
    }
    return 0;
}

int SqliteQueryEngine::getRecordCount( const wxString& key ){
    char *errmsg;

    wxString sql;
    int nRow, nColumn, result;
    char** dbResult;
    const char* _sql;
    sql.sprintf("SELECT * FROM preferences WHERE appId='%s' AND key='%s';", appId, key );
    _sql = sql.mb_str(wxConvUTF8);
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    //获得影响的记录数
    if( SQLITE_OK == result )
        return nRow;
    else {
#ifdef _DEBUG_
        printf("%s\n%s\n", _sql, errmsg);
#endif
        return -1;
    }
}

int SqliteQueryEngine::SetupItem( BaseItem* item ){
    char *errmsg;

    wxString sql;
    int nRow, nColumn, result;
    char** dbResult;
    char* field_value;
    int index;
    const char* _sql;
    //PreferenceViewType type;
    sql.sprintf("SELECT * FROM preferences WHERE appId='%s' AND key='%s';", appId, item->getKey() );
    _sql = sql.mb_str(wxConvUTF8);
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    //获得影响的记录数
    if( SQLITE_OK == result ) {
        //return nRow;
        if( nRow==0 ){
            sql.sprintf("insert into preferences ( appId, key, view_type, defaultValue, value, prompt, description ) values ( '%s', '%s', '%d', '%s', '%s', '%s', '%s' );",
                        appId, item->getKey(), item->getType(), item->getDefaultValue(), item->getValue(),
                        item->getPrompt(), item->getDescription() );
            _sql = sql.mb_str(wxConvUTF8);
            result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
            if( SQLITE_OK == result ) {
                return 0;
            }else
                return -2;
        }else if( nRow==1 ){
            //found, fill the item
            index = nColumn;
            char* id = dbResult[ index ];
            char* app_id = dbResult[ index +1 ];
            char* key = dbResult[ index + 2 ];
            char* _type = dbResult[ index + 3 ];
            char* defaultValue = dbResult[ index + 4 ];
            char* value = dbResult[ index + 5 ];
            char* prompt = dbResult[ index + 6 ];
            char* description = dbResult[ index + 7 ];
            item->setDefaultValue( defaultValue );
            item->setValue( value );
            int temp = atoi( _type );
            PreferenceViewType type;
            switch (temp) {
                case PT_INPUT:
                    type = PT_INPUT;
                    break;
                case PT_SWITCH:
                    type = PT_SWITCH;
                    break;
                case PT_LIST:
                    type = PT_LIST;
                    break;
            }
            item->setViewType( type );
            item->setPrompt( prompt );
            item->setDescription( description );
        }else{
            //
            wxString temp;
            temp.sprintf( wxT("Have duplicate key: %s, %s "), appId, item->getKey() );
            wxMessageBox( temp, wxT("错误"), wxICON_ERROR );
            return -3;
        }
    }else {
#ifdef _DEBUG_
        printf("%s\n%s\n", _sql, errmsg);
#endif
        return -1;
    }
}

/*
 * setting frame setup:
 *  sqlite engine setup
 *  find each keys, if no exist, insert
 *
 * first:
 *  setup
 * when use:
 *  BaseItem item;
 *  wxGetApp()->GetSettingInterface()->QueryItem( key, &item );
 */
int SqliteQueryEngine::Setup( wxVector<BaseItem*>* items ){
    char *errmsg;

    wxString sql;
    int nRow, nColumn, result;
    char** dbResult;
    const char* _sql;
    //BaseItem* item;
    for(auto * item : *items){
        //item = i;
        //if no exist "insert", if existed "select"
        result = SetupItem( item );
        if( result!=0 ){
            wxMessageBox( wxT("检测并初始化数据错误"), wxT("错误"), wxICON_ERROR );
            break;
        }
    }
    return result;
}

/*
 * frame control::onChange
 *  frame: UpdateItem( const wxString& key, const wxString& value );
 *  SqliteQueryEngine::UpdateItem( const wxString& key, const wxString& value )
 * */
int SqliteQueryEngine::UpdateItem( const wxString& key, const wxString& value ) {
    //如果不存在就建立
    char *errmsg;

    wxString sql;
    int nRow, nColumn, result;
    char** dbResult;
    const char* _sql;
    //check first run, null database, null table
    /*
    sql.sprintf("SELECT * FROM preferences WHERE appId='%s' AND key='%s';", appId, key );
    _sql  = sql.mb_str(wxConvUTF8);
    //
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    if( SQLITE_OK != result )
    {
        throw "Can't query the preferences from sqlite_master!";
        return -1;
    }else if( nRow>0 ){
        //found

    }else{
        //no find
    }*/
    sql.sprintf( "update preferences set `value`= '%s' where key='%s' and appId='%s';", value, key, appId );
    _sql = sql.mb_str(wxConvUTF8);
    //和sqlite3_exec()这种使用回调函数的方式不同，sqlite3_get_table()的功能要显得更加直
    //result = sqlite3_exec( m_pDb, _sql,nullptr, nullptr, &errmsg );
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    //获得影响的记录数
    if( SQLITE_OK == result )
        return 0;
    else {
#ifdef _DEBUG_
        printf("%s\n%s\n", _sql, errmsg);
#endif
        return -1;
    }
}

/*
 * Row2Item:
 *  dbResult
 *      currentIdx
 * dbResult and index
 *
 */
BaseItem * SqliteQueryEngine::Row2Item( char **dbResult, int nColumn, int rowIndex ) {
    //get column: dbResult[
    //index = nColumn;
    //int index = 0;
    bool _valueIsNull = false;
    //if rowIndex = 0, then get the fields name;
    int index = nColumn * ( rowIndex + 1 );
    wxString key, value, defaultValue, prompt, description;
    char* field_value;
    PreferenceViewType type;
    for( int j = 0 ; j < nColumn; j++ ) {
        field_value = dbResult[index];
        switch (j) {
            case COLUMN_ID:
                //id
                break;
            case COLUMN_APP_ID:
                //
                break;
            case COLUMN_KEY:
                key = field_value;
                break;
            case COLUMN_VIEW_TYPE: {
                int temp = atoi( field_value );
                switch (temp) {
                    case PT_INPUT:
                        type = PT_INPUT;
                        break;
                    case PT_SWITCH:
                        type = PT_SWITCH;
                        break;
                    case PT_LIST:
                        type = PT_LIST;
                        break;
                }
                break;
            }
            case COLUMN_DEFAULT_VALUE:
                if ( field_value == nullptr ) {
                    _valueIsNull = true;
                }
                defaultValue = field_value;
                break;
            case COLUMN_VALUE:
                value = field_value;
                break;
            case COLUMN_PROMPT:
                prompt = field_value;
                break;
            case COLUMN_DESCRIPTION:
                description = field_value;
                break;
        }
        ++index;
    }
    return new BaseItem( key, type, defaultValue, value, prompt, description, _valueIsNull );
}

int SqliteQueryEngine::Row2Item( char **dbResult, int nColumn, int rowIndex, BaseItem * item ) {
    int index = nColumn * ( rowIndex + 1 );
    wxString key, value, defaultValue, prompt, description;
    char* field_value;
    PreferenceViewType type;
    bool _valueIsNull = false;
    for( int j = 0 ; j < nColumn; j++ ) {
        field_value = dbResult[index];
        switch (j) {
            case COLUMN_ID:
                //id
                break;
            case COLUMN_APP_ID:
                //
                break;
            case COLUMN_KEY:
                //key = field_value;
                item->setValue( field_value );
                break;
            case COLUMN_VIEW_TYPE: {
                int temp = atoi( field_value );
                switch (temp) {
                    case PT_INPUT:
                        type = PT_INPUT;
                        break;
                    case PT_SWITCH:
                        type = PT_SWITCH;
                        break;
                    case PT_LIST:
                        type = PT_LIST;
                        break;
                }
                item->setViewType( type );
                break;
            }
            case COLUMN_DEFAULT_VALUE:
                if ( field_value == nullptr ) {
                    _valueIsNull = true;
                }
                item->setValueIsNull( _valueIsNull );
                //defaultValue = field_value;
                item->setDefaultValue( field_value );
                break;
            case COLUMN_VALUE:
                //value = field_value;
                item->setValue( field_value );
                break;
            case COLUMN_PROMPT:
                //prompt = field_value;
                item->setPrompt( field_value );
                break;
            case COLUMN_DESCRIPTION:
                //description = field_value;
                item->setDescription( field_value );
                break;
        }
        ++index;
    }
    return 0;
}

int SqliteQueryEngine::QueryItem( wxString &key, BaseItem* item ) {
    wxString sql;
    const char * _sql;
    char *errmsg;
    int result;

    int nRow, nColumn;
    int i , j;
    int index;
    char **dbResult; //是 char ** 类型，两个*号
    PreferenceViewType type;

    sql.sprintf("select * from preferences where appId='%s' and key='%s';", appId, key );
    _sql  = sql.mb_str( wxConvUTF8 );
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    wxString value, defaultValue;
    bool _valueIsNull;
    char* field_value;
    //
    if( SQLITE_OK == result ){
        if( nRow>0 ) {
            index = nColumn;
            //
            for( j =0; j<nColumn; j++ ){
                field_value = dbResult[index];
                switch (j) {
                    case COLUMN_ID:
                        //id
                        break;
                    case COLUMN_APP_ID:
                        //
                        break;
                    case COLUMN_KEY:
                        item->setKey(field_value);
                        break;
                    case COLUMN_VIEW_TYPE: {
                        int temp = atoi(field_value);
                        switch (temp) {
                            case PT_INPUT:
                                type = PT_INPUT;
                                break;
                            case PT_SWITCH:
                                type = PT_SWITCH;
                                break;
                            case PT_LIST:
                                type = PT_LIST;
                                break;
                        }
                        item->setViewType( type );
                        break;
                    }
                    case COLUMN_DEFAULT_VALUE:
                        if (dbResult[index] == nullptr) {
                            _valueIsNull = true;
                        }
                        item->setDefaultValue( field_value );
                        break;
                    case COLUMN_VALUE:
                        value = field_value;
                        break;
                    case COLUMN_PROMPT:
                        item->setPrompt( field_value );
                        break;
                    case COLUMN_DESCRIPTION:
                        item->setDescription( field_value );
                        break;
                }
                index++;
            }
        }
    }
    return 0;
}

int SqliteQueryEngine::FillItems(wxVector<BaseItem*> *items) {
    wxString sql;

    const char * _sql;
    //const char*
    //char errmsg[256] = {0};
    char *errmsg;
    int result;

    int nRow, nColumn;
    int i , j;
    int index;
    char **dbResult; //是 char ** 类型，两个*号
    char* field_value;
    //check first run, null database, null table
    sql.sprintf("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s';", "preferences" );
    _sql  = sql.mb_str(wxConvUTF8);
    //
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    if( SQLITE_OK != result )
    {
        throw "Can't query the preferences from sqlite_master!";
        return -1;
    }
    char* s = dbResult[1];
    wxString count = s;
    if( count.IsSameAs("0")){
        InitTable();
        //
        //this->myTagApp->getPreferenceCore()->OnWithoutPreferenceTable( this );
    }
    //
    //this->myTagApp->getPreferenceCore()->OnPreferencePreLoad(this);
    //query data
    sql.clear();
    //
    sql.sprintf("select * from preferences where appId='%s';", appId );
    _sql  = sql.mb_str(wxConvUTF8);
    //nRow, nColumn
    result = sqlite3_get_table( m_pDb, _sql, &dbResult, &nRow, &nColumn, &errmsg );
    wxString key, value, defaultValue, prompt, description;
    PreferenceViewType type;
    bool _valueIsNull;
    /*
     * dbResult layout:
     *  字符串数组
     * main( int argc, char** argv )
     * query the arg one: argv[0], argv[1]
     * argv:
     *  argv[0]
     *  argv[1]
     * 字符串数组
     * char* s[2] = { "str1", "str2" };
     * 内存里如何存放的，用零隔断?
     * */
    if( SQLITE_OK == result )
    {
        //查询成功
        //ignore 0->nColumn-1, it's the field name
        index = nColumn; //前面说过 dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据
        //printf( "查到%d条记录/n", nRow );

        //nRow
        for(  i = 0; i < nRow ; i++ )
        {
            field_value = dbResult[index];
            //?
            _valueIsNull = false;
            //printf( "第 %d 条记录/n", i+1 );
            for( j = 0 ; j < nColumn; j++ )
            {
                //
                switch (j) {
                    case COLUMN_ID:
                        //id
                        break;
                    case COLUMN_APP_ID:
                        //
                        break;
                    case COLUMN_KEY:
                        key = field_value;
                        break;
                    case COLUMN_VIEW_TYPE: {
                        int temp = atoi( field_value );
                        switch (temp) {
                            case PT_INPUT:
                                type = PT_INPUT;
                                break;
                            case PT_SWITCH:
                                type = PT_SWITCH;
                                break;
                            case PT_LIST:
                                type = PT_LIST;
                                break;
                        }
                        break;
                    }
                    case COLUMN_DEFAULT_VALUE:
                        if( dbResult [index] == nullptr ){
                            _valueIsNull = true;
                        }
                        defaultValue = field_value;
                        break;
                    case COLUMN_VALUE:
                        value = field_value;
                        break;
                    case COLUMN_PROMPT:
                        prompt = field_value;
                        break;
                    case COLUMN_DESCRIPTION:
                        description = field_value;
                        break;
                }
                /*
                 * int argc, char* argv[];
                 * row1: dbResult[0]: id, dbResult[1]:tag, dbResult[2]: key, dbResult[3]: defaultValue, dbResult[4]: value;
                 * row2: dbResult[5]: id
                 * ...
                 */
                //printf( "字段名:%s, 字段值:%s\n",  dbResult[j], dbResult [index] );
                //argv[index]
                //dbResult[index]
                ++index; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
            }
            //printf( "-------/n" );
            items->push_back( new BaseItem( key, type, defaultValue, value, prompt, description, _valueIsNull ) );
        }
    }else{
        //printf("%s\n", errmsg);
        wxString tmp = errmsg;
        wxMessageBox( tmp, wxT("错误"), wxICON_ERROR );
        sqlite3_free(errmsg);
        return -1;
    }
    //到这里，不论数据库查询是否成功，都释放 char** 查询结果，使用 sqlite 提供的功能来释放
    sqlite3_free_table( dbResult );
    return 0;
}


